package com.bookmanger.server.mapper;

import com.bookmanger.pojo.Entity.Book;
import com.bookmanger.pojo.Entity.ISBN;
import com.bookmanger.pojo.Entity.PreBook;
import com.bookmanger.pojo.Entity.Reader;
import com.bookmanger.pojo.VO.PrebookVO;
import org.apache.ibatis.annotations.*;

import java.time.LocalDateTime;
import java.util.List;

@Mapper
public interface PreBookMapper {
    

    @Select("SELECT * FROM book WHERE id = #{bookId}")
    Book getBookById(int bookId);
    

    @Select("SELECT * FROM prebook WHERE isbn_id = #{isbnId} AND status = 0 AND is_borrowed = 0 ORDER BY prebook_time ASC LIMIT 1")
    PreBook getEarliestPreBookByIsbnId(int isbnId);
    

    @Select("SELECT * FROM reader WHERE id = #{readerId}")
    Reader getReaderById(int readerId);
    

    @Insert("INSERT INTO prebook(reader_id, isbn_id, prebook_time, inform_time, status, is_borrowed, borrowed_book_id) " +
            "VALUES(#{readerId}, #{isbnId}, #{prebookTime}, #{informTime}, #{status}, #{isBorrowed}, #{borrowedBookId})")
    int insertPreBook(PreBook preBook);
    

    @Update("UPDATE prebook SET inform_time = #{informTime}, borrowed_book_id = #{bookId} WHERE id = #{id}")
    int updateInformTimeAndBookIdById(@Param("informTime") LocalDateTime informTime, @Param("bookId") int bookId, @Param("id") int id);

    /*
    * 取消预约
    * */
    @Update("UPDATE prebook SET status = 1 WHERE id = #{id}")
    int markAsCancelled(int id);
    
    /*
     * 标记预定记录为已完成
     **/
    @Update("UPDATE prebook SET is_borrowed = 1 WHERE id = #{id}")
    int markAsBorrowed(int id);
    
    /*
     * 获取所有超时的预定记录
     **/
    @Select("SELECT * FROM prebook WHERE status = 0 AND is_borrowed = 0 AND inform_time <= #{now}")
    List<PreBook> getOverduePreBooks(LocalDateTime now);
    
    /*
     * 根据预定ID获取预定记录
     **/
    @Select("SELECT * FROM prebook WHERE id = #{preId}")
    PreBook getPreBookById(int preId);
    
    /*
     * 更新借阅管理员ID
     **/
    @Update("UPDATE borrow SET borrow_manager_id = #{managerId} WHERE id = #{borrowTableId}")
    int updateBorrowManagerId(@Param("borrowTableId") int borrowTableId, @Param("managerId") int managerId);
    
    /*
     * 根据ISBN ID获取ISBN信息
     **/
    @Select("SELECT * FROM isbn_tb WHERE id = #{isbnId}")
    ISBN getIsbnById(int isbnId);
    
    /*
     * 图书归还设置为0
     **/
    @Update("UPDATE book SET is_pass = 0 WHERE id = #{bookId}")
    int updateBookIsPassToZero(int bookId);
    
    /*
     * 增加库存
     **/
    @Update("UPDATE isbn_tb SET stock = CASE WHEN stock + #{count} < 0 THEN 0 ELSE stock + #{count} END WHERE id = #{isbnId}")
    int increaseIsbnStock(@Param("isbnId") int isbnId, @Param("count") int count);
    
    /*
     * 查询is_borrowed=0且inform_time不为空的预定记录
     **/
    @Select("SELECT p.*, r.reader_number, r.name as reader_name, r.email, i.title, i.author, i.isbn, i.price, i.publisher, i.publisher_date " +
            "FROM prebook p " +
            "JOIN reader r ON p.reader_id = r.id " +
            "JOIN isbn_tb i ON p.isbn_id = i.id " +
            "WHERE p.is_borrowed = 0 AND p.inform_time IS NOT NULL AND p.status = 0 " +
            "ORDER BY p.inform_time ASC")
    List<PrebookVO> getUnborrowedPreBooks();
    
    /*
     * 查询is_borrowed=1且inform_time不为空的预定记录
     **/
    @Select("SELECT p.*, r.reader_number, r.name as reader_name, r.email, i.title, i.author, i.isbn, i.price, i.publisher, i.publisher_date " +
            "FROM prebook p " +
            "JOIN reader r ON p.reader_id = r.id " +
            "JOIN isbn_tb i ON p.isbn_id = i.id " +
            "WHERE p.is_borrowed = 1 AND p.inform_time IS NOT NULL AND p.status = 0 " +
            "ORDER BY p.inform_time ASC")
    List<PrebookVO> getBorrowedPreBooks();
    
    /*
     * 根据readerNumber或name模糊查询读者信息
     **/
    @Select("SELECT id FROM reader WHERE reader_number LIKE CONCAT('%', #{keyword}, '%') OR name LIKE CONCAT('%', #{keyword}, '%')")
    List<Integer> getReaderIdsByKeyword(@Param("keyword") String keyword);
    
    /*
     * 根据readerId列表查询相关的预定记录且inform_time不为空
     **/
    @Select("<script>" +
            "SELECT p.*, r.reader_number, r.name as reader_name, r.email, i.title, i.author, i.isbn, i.price, i.publisher, i.publisher_date " +
            "FROM prebook p " +
            "JOIN reader r ON p.reader_id = r.id " +
            "JOIN isbn_tb i ON p.isbn_id = i.id " +
            "WHERE p.inform_time IS NOT NULL AND p.status = 0 AND p.reader_id IN " +
            "<foreach item='readerId' collection='readerIds' open='(' separator=',' close=')'>" +
            "#{readerId}" +
            "</foreach>" +
            " ORDER BY p.inform_time ASC" +
            "</script>")
    List<PrebookVO> getPreBooksByReaderIds(@Param("readerIds") List<Integer> readerIds);
}